using SPI.AgentServer.Services.Execution;
using System.Text;

namespace SPI.AgentServer.Services.Tools;

/// <summary>
/// 车辆管理查询工具 - 查询车辆进出、停留、违规等信息
/// 覆盖场景：V-001 外来车辆在校, V-002 车辆进出记录, V-003 超速车辆, V-004 长时间停留, V-005 今天进校车辆, V-009 夜间进校车辆
/// </summary>
public class GetVehicleManagementTool : IQueryTool
{
    private readonly IDatabaseExecutor _executor;
    private readonly ILogger<GetVehicleManagementTool> _logger;

    public string Name => "get_vehicle_management";
    public string Description => "查询车辆进出和管理信息，支持按车牌号、时间范围、车辆类型筛选。用于回答'外来车辆在校'、'车辆进出记录'、'超速车辆'、'长时间停留车辆'等问题。";

    public GetVehicleManagementTool(
        IDatabaseExecutor executor,
        ILogger<GetVehicleManagementTool> logger)
    {
        _executor = executor;
        _logger = logger;
    }

    public bool ValidateParameters(Dictionary<string, object> parameters, out List<string> errors)
    {
        errors = new List<string>();
        return true;
    }

    public async Task<ToolResult> ExecuteAsync(Dictionary<string, object> parameters, CancellationToken cancellationToken = default)
    {
        if (parameters == null)
        {
            return new ToolResult
            {
                Success = false,
                Errors = new List<string> { "参数不能为空" }
            };
        }

        try
        {
            // 获取参数
            string? plateNumber = parameters.ContainsKey("plateNumber") ? parameters["plateNumber"]?.ToString() : null;
            string? startTime = parameters.ContainsKey("startTime") ? parameters["startTime"]?.ToString() : null;
            string? endTime = parameters.ContainsKey("endTime") ? parameters["endTime"]?.ToString() : null;
            string queryType = parameters.ContainsKey("queryType") ? parameters["queryType"]?.ToString() ?? "all" : "all";
            int? stayDays = parameters.ContainsKey("stayDays") ? Convert.ToInt32(parameters["stayDays"]) : null;

            // 构建SQL
            var sql = queryType switch
            {
                "on_campus" => BuildOnCampusVehiclesQuery(),
                "speeding" => BuildSpeedingVehiclesQuery(startTime),
                "long_stay" => BuildLongStayVehiclesQuery(stayDays ?? 3),
                "night_entry" => BuildNightEntryVehiclesQuery(startTime),
                "count" => BuildVehicleCountQuery(startTime),
                _ => BuildVehicleRecordsQuery(plateNumber, startTime, endTime)
            };

            _logger.LogInformation("Executing vehicle management query with SQL: {Sql}", sql);

            var result = await _executor.ExecuteQueryAsync(sql, null, cancellationToken);

            return new ToolResult
            {
                Success = true,
                Data = result.Data,
                Message = "车辆管理查询成功",
                Metadata = new Dictionary<string, object>
                {
                    ["executionTimeMs"] = result.ExecutionTimeMs,
                    ["executedSql"] = sql,
                    ["queryType"] = queryType
                }
            };
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error executing vehicle management query");
            return new ToolResult
            {
                Success = false,
                Errors = new List<string> { $"查询失败: {ex.Message}" }
            };
        }
    }

    /// <summary>
    /// 构建车辆进出记录查询
    /// </summary>
    private string BuildVehicleRecordsQuery(string? plateNumber, string? startTime, string? endTime)
    {
        var sb = new StringBuilder();

        sb.AppendLine("SELECT");
        sb.AppendLine("    PlateNumber AS plate_number,");
        sb.AppendLine("    Owner AS owner,");
        sb.AppendLine("    InTime AS in_time,");
        sb.AppendLine("    OutTime AS out_time,");
        sb.AppendLine("    CASE");
        sb.AppendLine("        WHEN OutTime IS NULL THEN '在校'");
        sb.AppendLine("        ELSE '已离校'");
        sb.AppendLine("    END AS status,");
        sb.AppendLine("    InEntrance AS in_entrance,");
        sb.AppendLine("    OutEntrance AS out_entrance,");
        sb.AppendLine("    CASE Temp1");
        sb.AppendLine("        WHEN '2' THEN '访客'");
        sb.AppendLine("        ELSE '其他'");
        sb.AppendLine("    END AS vehicle_type");
        sb.AppendLine("FROM TblMotorVehicleManagement");
        sb.AppendLine("WHERE 1=1");

        if (!string.IsNullOrEmpty(plateNumber))
        {
            sb.AppendLine($"    AND PlateNumber = '{plateNumber}'");
        }

        if (!string.IsNullOrEmpty(startTime))
        {
            sb.AppendLine($"    AND DATE(InTime) >= '{startTime}'");
        }

        if (!string.IsNullOrEmpty(endTime))
        {
            sb.AppendLine($"    AND DATE(InTime) <= '{endTime}'");
        }

        sb.AppendLine("ORDER BY InTime DESC");
        sb.AppendLine("LIMIT 100");

        return sb.ToString();
    }

    /// <summary>
    /// 构建在校外来车辆查询
    /// </summary>
    private string BuildOnCampusVehiclesQuery()
    {
        var sb = new StringBuilder();

        sb.AppendLine("SELECT");
        sb.AppendLine("    PlateNumber AS plate_number,");
        sb.AppendLine("    Owner AS owner,");
        sb.AppendLine("    InTime AS in_time,");
        sb.AppendLine("    TIMESTAMPDIFF(HOUR, InTime, NOW()) AS stay_hours,");
        sb.AppendLine("    ROUND(TIMESTAMPDIFF(HOUR, InTime, NOW()) / 24.0, 1) AS stay_days,");
        sb.AppendLine("    InEntrance AS in_entrance");
        sb.AppendLine("FROM TblMotorVehicleManagement");
        sb.AppendLine("WHERE OutTime IS NULL");
        sb.AppendLine("    AND Temp1 = '2'"); // 2=访客
        sb.AppendLine("ORDER BY InTime ASC");
        sb.AppendLine("LIMIT 100");

        return sb.ToString();
    }

    /// <summary>
    /// 构建超速车辆查询
    /// </summary>
    private string BuildSpeedingVehiclesQuery(string? startTime)
    {
        var sb = new StringBuilder();

        var queryDate = string.IsNullOrEmpty(startTime) ? DateTime.Today.ToString("yyyy-MM-dd") : startTime;

        sb.AppendLine("SELECT");
        sb.AppendLine("    PlateNumber AS plate_number,");
        sb.AppendLine("    Owner AS owner,");
        sb.AppendLine("    Speed AS speed,");
        sb.AppendLine("    OverSpeedArea AS location,");
        sb.AppendLine("    CreateTime AS create_time");
        sb.AppendLine("FROM TblMotorVehicleManagement");
        sb.AppendLine("WHERE ViolationType = 1"); // 1=超速
        sb.AppendLine($"    AND DATE(CreateTime) >= '{queryDate}'");
        sb.AppendLine("ORDER BY Speed DESC, CreateTime DESC");
        sb.AppendLine("LIMIT 50");

        return sb.ToString();
    }

    /// <summary>
    /// 构建长时间停留车辆查询
    /// </summary>
    private string BuildLongStayVehiclesQuery(int stayDays)
    {
        var sb = new StringBuilder();

        sb.AppendLine("SELECT");
        sb.AppendLine("    PlateNumber AS plate_number,");
        sb.AppendLine("    Owner AS owner,");
        sb.AppendLine("    InTime AS in_time,");
        sb.AppendLine("    TIMESTAMPDIFF(DAY, InTime, NOW()) AS stay_days,");
        sb.AppendLine("    TIMESTAMPDIFF(HOUR, InTime, NOW()) AS stay_hours,");
        sb.AppendLine("    InEntrance AS in_entrance");
        sb.AppendLine("FROM TblMotorVehicleManagement");
        sb.AppendLine("WHERE OutTime IS NULL");
        sb.AppendLine($"    AND TIMESTAMPDIFF(DAY, InTime, NOW()) > {stayDays}");
        sb.AppendLine("ORDER BY stay_days DESC");
        sb.AppendLine("LIMIT 50");

        return sb.ToString();
    }

    /// <summary>
    /// 构建夜间进校车辆查询
    /// </summary>
    private string BuildNightEntryVehiclesQuery(string? startTime)
    {
        var sb = new StringBuilder();

        var queryDate = string.IsNullOrEmpty(startTime)
            ? DateTime.Today.AddDays(-1).ToString("yyyy-MM-dd")
            : DateTime.Parse(startTime).ToString("yyyy-MM-dd");

        sb.AppendLine("SELECT");
        sb.AppendLine("    PlateNumber AS plate_number,");
        sb.AppendLine("    Owner AS owner,");
        sb.AppendLine("    InTime AS in_time,");
        sb.AppendLine("    InEntrance AS in_entrance,");
        sb.AppendLine("    CASE");
        sb.AppendLine("        WHEN OutTime IS NULL THEN '在校'");
        sb.AppendLine("        ELSE '已离校'");
        sb.AppendLine("    END AS status");
        sb.AppendLine("FROM TblMotorVehicleManagement");
        sb.AppendLine($"WHERE InTime >= '{queryDate} 22:00:00'");
        sb.AppendLine($"    AND InTime < DATE_ADD('{queryDate}', INTERVAL 1 DAY) + INTERVAL 6 HOUR");
        sb.AppendLine("ORDER BY InTime DESC");
        sb.AppendLine("LIMIT 50");

        return sb.ToString();
    }

    /// <summary>
    /// 构建车辆进校数量统计
    /// </summary>
    private string BuildVehicleCountQuery(string? startTime)
    {
        var sb = new StringBuilder();

        var queryDate = string.IsNullOrEmpty(startTime) ? DateTime.Today.ToString("yyyy-MM-dd") : startTime;

        sb.AppendLine("SELECT");
        sb.AppendLine("    COUNT(*) AS entry_count,");
        sb.AppendLine($"    '{queryDate}' AS query_date");
        sb.AppendLine("FROM TblMotorVehicleManagement");
        sb.AppendLine($"WHERE DATE(InTime) = '{queryDate}'");

        return sb.ToString();
    }
}
